﻿using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using Mono.Data.Sqlite;//引入数据库
using System.IO;


public class SqliteHelper {

    /// <summary>
    /// 定义私有构造器 单例
    /// </summary>
	private SqliteHelper() { }

    /// <summary>
    /// 单例对象
    /// </summary>
    private static SqliteHelper instance;

    /// <summary>
    /// 获取单例
    /// </summary>
    /// <returns>单例对象</returns>
    public static SqliteHelper GetInstance()
    {
        if(instance == null)
        {
            instance = new SqliteHelper();
        }
        return instance;
    }

    /// <summary>
    /// 数据库链接对象
    /// </summary>
    private SqliteConnection con;
    /// <summary>
    /// 数据库指令对象
    /// </summary>
    private SqliteCommand command;
    /// <summary>
    /// 数据库读取对象
    /// </summary>
    private SqliteDataReader reader;
    
    /// <summary>
    /// 找到数据库 打开链接 实例化指令对象
    /// </summary>
    /// <param name="dataBaseName">数据库名字</param>
    public void ConnectionToSqlite(string dataBaseName)
    {
        try
        {
            if(con == null)
            {
                if (!dataBaseName.Contains(".sqlite"))
                {
                    dataBaseName += ".sqlite";
                }
                string connectionStr = "";


#if UNITY_ANDROID
                //用户使用数据库链接的地址
                connectionStr = "URL=file;" + Application.persistentDataPath + "/" + dataBaseName;
                //android程序包的文件路径
                string androidPath = "jar:file://" + Application.dataPath + "!/assets/" + dataBaseName;
                //文件存储位置
                string androidFilePath = Application.persistentDataPath + "/" + dataBaseName;

                //判断数据库文件是否存在
                if(!File.Exists(androidFilePath))
                {
                    WWW www = new WWW(androidPath);
                    while (!www.isDone) { }
                    File.WriteAllBytes(androidFilePath, www.bytes);
                }
#endif

#if UNITY_IOS
                connectionStr = "Data source=" + Application.persistentDataPath + "/" + dataBaseName;
#endif

#if UNITY_EDITOR
                connectionStr = "Data source=" + Application.streamingAssetsPath + "/" + dataBaseName;
#endif

#if UNITY_STANDALONE
                connectionStr = "Data source=" + Application.streamingAssetsPath + "/" + dataBaseName;
#endif
                try
                {
                    //Debug.Log(connectionStr);//路径
                    con = new SqliteConnection(connectionStr);
                    con.Open();
                }
                catch (SqliteException e)
                {
                    Debug.Log(e);
                }
            }
            if(command == null)
            {
                command = con.CreateCommand();
            }
        }
        catch(SqliteException e)
        {
            Debug.Log(e);
        }
    }


    /// <summary>
    /// 关闭链接 释放资源
    /// </summary>
    public void CloseConnection()
    {
        if(command != null)
        {
            //销毁指令
            command.Dispose();
        }
        if(con != null)
        {
            //关闭链接
            con.Close();
        }
    }

    /// <summary>
    /// 增删改操作方法
    /// </summary>
    /// <param name="cmdText">增删改的sql语句</param>
    public void IDU(string cmdText)
    {
        try
        {
            command.CommandText = cmdText;
            command.ExecuteNonQuery();
        }
        catch (SqliteException e)
        {
            Debug.Log(e);
        }
    }

    /// <summary>
    /// 获取第一个数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="colName">字段名</param>
    /// <returns></returns>
    public object SelectONE(string tableName,string colName)
    {
        try
        {
            string cmdText = "select " + colName + " from " + tableName;
            command.CommandText = cmdText;
            //返回一个数据
            object obj = command.ExecuteScalar();
            return obj;
        }
        catch(SqliteException e)
        {
            Debug.Log(e);
            return null;
        }
    }

   
    /// <summary>
    /// 获取部分数据
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="colName">所有字段名</param>
    /// <returns>查找的部分数据</returns>
    public SqliteDataReader SelectPARTS(string tableName, params string[] colName)
    {
        try
        {
            string cols = "";
            foreach(string item in colName)
            {
                cols += item + ",";
            }
            cols = cols.Remove(cols.Length - 1, 1);
            string cmdText = "select " + cols + " from " + tableName;
            command.CommandText = cmdText;
            reader = command.ExecuteReader();
            return reader;
        }
        catch(SqliteException e)
        {
            Debug.Log(e);
            return null;
        }
    }

    /// <summary>
    /// 获取全部数据
    /// </summary>
    /// <param name="tableName"></param>
    /// <returns>查找的全部数据</returns>
    public SqliteDataReader SelectALL(string tableName)
    {
        try
        {
            string cmdText = "select * from " + tableName;
            command.CommandText = cmdText;
            reader = command.ExecuteReader();
            return reader;
        }
        catch (SqliteException e)
        {
            Debug.Log(e);
            return null;
        }
    }

    /// <summary>
    /// 获取某一行数据
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="name"></param>
    /// <returns></returns>
    public SqliteDataReader SelectRow(string tableName, string colName, string name)
    {
        try
        {
            string cmdText = "select * from " + tableName + " where " + colName + " = '" + name + "'";
            command.CommandText = cmdText;
            reader = command.ExecuteReader();
            return reader;
        }
        catch (SqliteException e)
        {
            Debug.Log(e);
            return null;
        }
    }

    /// <summary>
    /// 查询数据
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns>查询数据</returns>
    public SqliteDataReader Select(string sql)
    {
        try
        {
            command.CommandText = sql;
            reader = command.ExecuteReader();
            return reader;
        }
        catch (SqliteException e)
        {
            Debug.Log(e);
            return null;
        }
    }

    /// <summary>
    /// 读取数据(只读一行)
    /// </summary>
    /// <param name="reader"></param>
    /// <returns></returns>
    public string[] ReadData(SqliteDataReader reader)
    {
        string[] strs = null;

        while (reader.Read())
        {
            strs = new string[reader.FieldCount];
            for (int i = 0; i < reader.FieldCount; i++)
            {
                strs[i] = reader.GetValue(i).ToString();
            }
            break;
        }
        reader.Close();
        return strs;
    }


    /// <summary>
    /// 获取一列
    /// </summary>
    /// <param name="reader">数据库</param>
    /// <param name="oneCol">列名</param>
    /// <returns></returns>
    public string[] ReadData(SqliteDataReader reader, string colName)
    {
        List<string> dataList = new List<string>();
        int i = 0;
        while (reader.Read())
        {
            dataList.Add(reader[colName].ToString());
            i++;
        }
        string[] strs = new string[i];
        for(int j = 0; j < i; j++)
        {
            strs[j] = dataList[j];
        }
        reader.Close();
        return strs;
    }


    /// <summary>
    /// 获取一列
    /// </summary>
    /// <param name="reader"></param>
    /// <param name="oneCol"></param>
    /// <returns></returns>
    public string[] ReadData(SqliteDataReader reader, bool oneCol)
    {
        List<string> dataList = new List<string>();
        int i = 0;
        while (reader.Read())
        {
            dataList.Add(reader.GetValue(0).ToString());
            i++;
        }
        string[] strs = new string[i];
        for (int j = 0; j < i; j++)
        {
            strs[j] = dataList[j];
        }
        reader.Close();
        return strs;
    }


    /// <summary>
    /// 获取某一个数据
    /// </summary>
    /// <param name="term">要查的东西名</param>
    /// <param name="tableName">表名</param>
    /// <param name="colName">行名</param>
    /// <param name="name">用户名</param>
    /// <returns></returns>
    public object SelectRow(string term, string tableName, string colName, string name)
    {
        try
        {
            string cmdText = "select " + term + " from " + tableName + " where " + colName + " = '" + name + "'";
            command.CommandText = cmdText;
            object obj = command.ExecuteScalar();
            return obj;
        }
        catch (SqliteException e)
        {
            Debug.Log(e);
            return null;
        }
    }

    /// <summary>
    /// 更新某一个string数据
    /// </summary>
    /// <param name="term">要更新的东西名</param>
    /// <param name="tableName">表名</param>
    /// <param name="colName">当前值</param>
    /// <param name="name">用户名</param>
    /// <returns></returns>
    public object UpdateOneData(string term, string tableName, string prop, string name)
    {
        try
        {
            string cmdText = "update " + tableName + " set " + term + " = '" + prop + "' where userName = '" + name + "'";
            command.CommandText = cmdText;
            object obj = command.ExecuteScalar();
            return obj;
        }
        catch (SqliteException e)
        {
            Debug.Log(e);
            return null;
        }
    }

    /// <summary>
    /// 更新某一个bool数据
    /// </summary>
    /// <param name="term">要更新的东西名</param>
    /// <param name="tableName">表名</param>
    /// <param name="colName">当前值</param>
    /// <param name="name">用户名</param>
    /// <returns></returns>
    public object UpdateOneData(string term, string tableName, bool curr, string name)
    {
        try
        {
            string cmdText = "update " + tableName + " set " + term + " = " + curr + "' where userName = '" + name + "'";
            command.CommandText = cmdText;
            object obj = command.ExecuteScalar();
            return obj;
        }
        catch (SqliteException e)
        {
            Debug.Log(e);
            return null;
        }
    }

    /// <summary>
    /// 更新某一个int数据
    /// </summary>
    /// <param name="term">要更新的东西名</param>
    /// <param name="tableName">表名</param>
    /// <param name="colName">当前值</param>
    /// <param name="name">用户名</param>
    /// <returns></returns>
    public object UpdateOneData(string term, string tableName, int curr, string name)
    {
        try
        {
            string cmdText = "update " + tableName + " set " + term + " = " + curr + "' where userName = '" + name + "'";
            command.CommandText = cmdText;
            object obj = command.ExecuteScalar();
            return obj;
        }
        catch (SqliteException e)
        {
            Debug.Log(e);
            return null;
        }
    }
}
